package com.b2c.repository;

import com.b2c.entity.result.PagingResponse;
import com.b2c.entity.GoodsCenterEntity;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import java.util.ArrayList;
import java.util.List;

/**
 * 描述：
 *
 * @author qlp
 * @date 2019-11-08 17:33
 */
@Repository
public class GoodsCenterRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    protected int getTotalSize() {
        return jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);
    }

    @Transactional
    public PagingResponse<GoodsCenterEntity> getList(Integer pageIndex, Integer pageSize, String goodsNumber) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT SQL_CALC_FOUND_ROWS g.* ");
        sb.append(",ali.productID as aliProductId,ali.subject as aliTitle,ali.mainImage as aliImage");//阿里商品
        sb.append(",yg.id as ygProductId,yg.title as ygTitle,yg.image as ygImage");//查询云购商品
        sb.append(",pdd.productId as pddProductId,pdd.title as pddTitle,pdd.mainImage as pddImage");//查询拼多多商品
//        sb.append(",IFNULL((select productID from "+Tables.DcAliGoods+" where erp_goods_id=g.id),0) as aliProductId ");//查询阿里商品列表
//        sb.append(",IFNULL((select id from "+Tables.Goods+" where erp_goods_id=g.id),0) as ygProductId ");//查询云购商品列表
//        sb.append(",IFNULL((select productId from "+Tables.DcPddGoods+" where erp_goods_id=g.id),0) as pddProductId ");//查询拼多多商品列表

        sb.append(",(select SUM(currentQty) from " + Tables.ErpGoodsSpec + " where goodsId=g.id) as currentQty ");//库存

        sb.append(" FROM ").append(Tables.ErpGoods + " g ");
        sb.append(" LEFT JOIN  ").append(Tables.DcAliGoods + " ali on ali.erp_goods_id = g.id ");//查询阿里商品
        sb.append(" LEFT JOIN  ").append(Tables.Goods + " yg on yg.erp_goods_id = g.id ");//查询云购商品
        sb.append(" LEFT JOIN  ").append(Tables.DcPddGoods + " pdd on pdd.erp_goods_id = g.id ");//查询拼多多商品

        sb.append(" where 1=1 ");
        List<Object> params = new ArrayList<>();
        if (StringUtils.isEmpty(goodsNumber) == false) {
            sb.append(" AND g.number = ?");
            params.add(goodsNumber);
        }
        sb.append(" ORDER BY g.id desc LIMIT ?,? ");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        List<GoodsCenterEntity> list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(GoodsCenterEntity.class), params.toArray(new Object[params.size()]));
        Integer totalSize = getTotalSize();
        return new PagingResponse<>(pageIndex, pageSize, totalSize, list);

    }


}
